IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'spPressreleasesSelect') AND type in (N'P', N'PC'))
	DROP PROCEDURE spPressreleasesSelect
GO
-- =============================================
-- Author:		NamTP
-- Create date: Oct 9, 2014
-- Description:	Select all pressrelease
-- Test: EXEC spPressreleasesSelect 'SocialNetwork',32, 14,'',null,null,0,8
-- =============================================
CREATE PROCEDURE spPressreleasesSelect
	@SourceCode VARCHAR(150),
	@LangId INT = 32,--32: English, 105: Vietnamese,
	@SourceId INT,
	@SearchPharse NVARCHAR(350) = NULL,
	@FromDate DATETIME = NULL,
	@ToDate DATETIME = NULL,
	@NumberFrom INT = NULL,
	@NumberTo INT = NULL
AS
BEGIN
	DECLARE @HasSearch BIT
	IF @SearchPharse IS NULL OR LEN(@SearchPharse) = 0
		BEGIN
			SET @HasSearch = 0			
		END
	ELSE
		BEGIN
			-- Adds in the LIKE wildcards for the @strSearchPhrase + prepares the strSearchPhrase for the ESCAPE carachter '\'
			SET @SearchPharse = '%' + REPLACE(REPLACE(@SearchPharse, '\', '\\'), '%', '\%') + '%'; 
			SET @HasSearch = 1;
		END	
		
	IF @ToDate IS NULL
		BEGIN
			SET @ToDate = GETDATE()
		END			

	IF @FromDate IS NULL
		BEGIN
			SET @FromDate = DATEADD(YEAR,-1,@ToDate)
		END	
		
	DECLARE @TotalRow INT	
	
	CREATE TABLE #Results  (
		[Id] BIGINT
		,[DateTime] DATETIME
		,[Title] NVARCHAR(500)
		,[Message] NVARCHAR(MAX)
		,[Attachment] BIT
		,[TypeId] INT
		,[SourceId] INT
		,[SourceCode] VARCHAR(150)
		,[LangId] INT
	);
	
	INSERT INTO #Results ([Id], [DateTime], [Title], [Message], [Attachment], [TypeId], [SourceId],[SourceCode],[LangId])
	SELECT [Id]	  	  
      ,[DateTime]      
      ,[Title]
      ,[Message]      
      ,[Attachment]
      ,[MessageTypeId]
      ,[SourceId]   
      ,[SourceCode]     
      ,[LangId]    
  FROM [dbo].[Pressreleases]  
  WHERE 
		[DateTime] BETWEEN @FromDate AND @ToDate		
		AND [SourceCode] = @SourceCode
		AND [SourceId] = @SourceId
		AND [LangId] = @LangId
		AND Published = 1
		AND (
			@HasSearch = 0
			OR (
				@HasSearch = 1
				AND (
					[Title] LIKE @SearchPharse ESCAPE '\'
					OR 
					[Message] LIKE @SearchPharse ESCAPE '\'
				)
			)
		)
		
	SELECT @TotalRow = COUNT(DateTime) FROM #Results
	SELECT @TotalRow AS TotalRowCount
	
	IF @NumberFrom IS NULL 
		BEGIN
			SET @NumberFrom = 1
		END	
	
	IF @NumberTo IS NULL
		BEGIN
			SET @NumberTo = @TotalRow
		END	
	
	CREATE TABLE #OutputResults (
		[Id] BIGINT
		,[DateTime] DATETIME
		,[Title] NVARCHAR(500)
		,[Message] NVARCHAR(MAX)
		,[Attachment] BIT
		,[TypeId] INT
		,[SourceId] INT
		,[SourceCode] VARCHAR(150)
		,[LangId] INT
	);
		
	WITH RESULT AS(
		SELECT ROW_NUMBER() OVER (ORDER BY [DateTime] ASC) AS NR, *
		FROM #Results
	) 
	INSERT INTO #OutputResults ([Id], [DateTime], [Title], [Message], [Attachment], [TypeId], [SourceId],[SourceCode],[LangId])
	SELECT [Id]
		,[DateTime]
		,[Title]
		,[Message]
		,[Attachment]
		,[TypeID]
		,[SourceId]
		,[SourceCode]
		,[LangId]
	FROM RESULT
	WHERE NR BETWEEN @NumberFrom AND @NumberTo
	ORDER BY NR ASC
	
	DROP TABLE #Results;
	
	SELECT * FROM #OutputResults;
	
	-- Gets the Attachments
	SELECT
		CAST(A.Id AS BIGINT) AS Id
		,CAST(A.MediaBinary AS VARBINARY(MAX)) AS MediaBinary
		,A.MimeType
		,CAST(R.Id AS BIGINT) AS PressreleaseId
		,A.FileName
		,A.Location
		,A.LangId
	FROM
		#OutputResults AS R
		JOIN
		Attachment AS A
			ON A.PressreleaseId = R.Id
	WHERE
		R.Attachment = 1
		AND A.LangId = @LangId
		AND A.IsBlob = 1
	ORDER BY
		A.PressreleaseId
		,A.DisplayOrder DESC --returned in DESC because are read in bacwards
	;	
	
	DROP TABLE #OutputResults;
	
END
GO
